package io.github.yangyouwang.crud.app.controller;

import io.github.yangyouwang.common.annotation.CrudLog;
import io.github.yangyouwang.common.base.CrudController;
import io.github.yangyouwang.common.domain.Result;
import io.github.yangyouwang.common.domain.TableDataInfo;
import io.github.yangyouwang.common.enums.BusinessType;
import io.github.yangyouwang.crud.app.entity.Device;
import io.github.yangyouwang.crud.app.entity.Info;
import io.github.yangyouwang.crud.app.entity.Site;
import io.github.yangyouwang.crud.app.service.DeviceService;
import io.github.yangyouwang.crud.app.service.InfoService;
import io.github.yangyouwang.crud.app.service.SiteService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiImplicitParams;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.validation.BindingResult;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.validation.Valid;
import javax.validation.constraints.NotNull;
import java.io.IOException;
import java.util.*;

/**
 * <p>
 * 信息表 前端控制器
 * </p>
 */
@Api(tags = "信息表")
@Controller
@RequestMapping("/app/ad")
public class AdController extends CrudController {
    private static final String SUFFIX = "app/ad";
    @Autowired
    private SiteService siteService;

    @Autowired
    private DeviceService deviceService;

    @Autowired
    private InfoService infoService;

    @GetMapping("/listPage")
    public String listPage() {
        return SUFFIX + "/list";
    }

    @ApiOperation(value = "信息表分页列表", response = Site.class)
    @ApiImplicitParams({
            @ApiImplicitParam(name = "page", value = "第几页", dataType = "Integer", paramType = "query"),
            @ApiImplicitParam(name = "limit", value = "每页记录数", dataType = "Integer", paramType = "query")
    })
    @PreAuthorize("hasAuthority('ad:list')")
    @GetMapping(value = "/page")
    @ResponseBody
    public TableDataInfo page(Site param) {
        startPage();
        List<Site> data = siteService.page(param);
        return getDataTable(data);
    }

    @ApiOperation(value = "信息表分页列表2", response = Site.class)
    @ApiImplicitParams({
            @ApiImplicitParam(name = "page", value = "第几页", dataType = "Integer", paramType = "query"),
            @ApiImplicitParam(name = "limit", value = "每页记录数", dataType = "Integer", paramType = "query")
    })
    @PreAuthorize("hasAuthority('ad:list')")
    @GetMapping(value = "/page2")
    @ResponseBody
    public TableDataInfo page2(Site param) {
        startPage();
        List<Site> data = siteService.page2(param);
        return getDataTable(data);
    }

    @GetMapping("/editPage/{id}")
    public String editPage(@Valid @NotNull(message = "id不能为空") @PathVariable Long id, ModelMap map) {
        Object data = siteService.info(id);
        map.put("ad", data);
        return SUFFIX + "/edit";
    }

    @GetMapping("/addPage")
    public String addPage() {
        return SUFFIX + "/add";
    }

    @ApiOperation(value = "信息表新增")
    @PreAuthorize("hasAuthority('ad:add')")
    @PostMapping(value = "/add")
    @ResponseBody
    @CrudLog(title = "新增信息", businessType = BusinessType.INSERT)
    public Result add(@RequestBody @Validated Map param, BindingResult bindingResult) {
        if (bindingResult.hasErrors()) {
            return Result.failure(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
        }
        siteService.add(param);
        deviceService.add(param);
        return Result.success();
    }

    @ApiOperation(value = "信息表修改")
    @PreAuthorize("hasAuthority('ad:edit')")
    @PostMapping(value = "/modify")
    @ResponseBody
    @CrudLog(title = "修改信息", businessType = BusinessType.UPDATE)
    public Result modify(@RequestBody @Validated Map param, BindingResult bindingResult) {
        if (bindingResult.hasErrors()) {
            return Result.failure(Objects.requireNonNull(bindingResult.getFieldError()).getDefaultMessage());
        }
        siteService.modify(param);
        deviceService.modify(param);
        return Result.success();
    }

    @ApiOperation(value = "信息表删除(单个条目)")
    @PreAuthorize("hasAuthority('ad:del')")
    @DeleteMapping(value = "/remove/{id}")
    @ResponseBody
    @CrudLog(title = "删除信息", businessType = BusinessType.DELETE)
    public Result remove(@Valid @NotNull(message = "id不能为空") @PathVariable Long id) {
        siteService.remove(id);
        return Result.success();
    }

    @ApiOperation(value = "信息表删除(多个条目)")
    @PreAuthorize("hasAuthority('ad:del')")
    @PostMapping(value = "/removes")
    @ResponseBody
    @CrudLog(title = "删除信息", businessType = BusinessType.DELETE)
    public Result removes(@RequestBody @Valid List<Long> ids) {
        siteService.removes(ids);
        return Result.success();
    }


    @GetMapping("/infoPage/{id}")
    public String infoPage(@Valid @NotNull(message = "id不能为空") @PathVariable Long id, ModelMap map) {
        //原为site改为map来接收参数了
        Map data = siteService.info(id);
        map.put("info", data);
        return SUFFIX + "/info";
    }

    @GetMapping("/infoPage2/{id}")
    public String infoPage2(@Valid @NotNull(message = "id不能为空") @PathVariable Long id, ModelMap map) {
        //原为site改为map来接收参数了
        Map data = siteService.info2(id);
        map.put("info", data);
        return "app/user/info";
    }


    @GetMapping(value = "/pageTest")
    @ResponseBody
    public List pageTest() {
        List list = siteService.selectShow();
        return list;
    }

    @PostMapping(value = "/pageTest2")
    @ResponseBody
    public List pageTest2(@RequestBody @Validated Map param) {
        List list = siteService.selectShow2(param);
        return list;
    }

    @PostMapping(value = "/pageTest3")
    @ResponseBody
    public List pageTest3(@RequestBody @Validated Map param) {
        List list = siteService.selectShow3(param);
        return list;
    }

    @PostMapping(value = "/tableTest")
    @ResponseBody
    public TableDataInfo pageTest4(@RequestBody Map param) {
        List<Site> list = siteService.getTable(param);
        return getDataTable(list);
    }

    @GetMapping(value = "/tableTest2")
    @ResponseBody
    public TableDataInfo pageTest5(Device param) {
        List<Device> list = siteService.getTable2(param);
        return getDataTable(list);
    }
    /**
     * 饼图
     */
    @PostMapping(value = "/show")
    @ResponseBody
    public List show(Device param) {
        List echartsList = siteService.getTable2(param);
        return echartsList;
    }

    /**
     * 饼图2公共属性
     */
    @PostMapping(value = "/show2")
    @ResponseBody
    public List show2(@RequestBody @Validated Map param) {
        List echartsList = siteService.getTable3(param);
        return echartsList;
    }

    // 导入Excel
    @PostMapping("importExcel")
    @ResponseBody //importExcel是弹出框name值
    public Map<String, Object> importExcel(@RequestParam("file") MultipartFile importExcel){
        Map<String, Object> map = new HashMap<>();
        try {
            //1.将文件封装成工作薄
            XSSFWorkbook workbook = new XSSFWorkbook(importExcel.getInputStream());
            //2.获取工作表的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            //3.循环所有的工作表，根据工作表下标获取对应的sheet
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = workbook.getSheetAt(i);
                // 4.获取当前工作表中数据的开始位置
                int firstRowNum = sheet.getFirstRowNum();
                // 5.获取当前工作表中数据的结束位置
                int lastRowNum = sheet.getLastRowNum();
                // 6.将文件中的数据封装为一个list保存到数据库中
                List<Site> list = new ArrayList<Site>();
                for (int j = firstRowNum+1; j <= lastRowNum; j++) {
                    // 获取sheet中的每一行
                    XSSFRow row = sheet.getRow(j);
                    // 获取行中单元格的值
                    Site site = getExcelSite(row);
                    Info info = getExcelInfo(row);
                    //插入
                    siteService.ImportAdd(site);
                    info.setSiteId(String.valueOf(site.getId()));
                    infoService.ImportAdd(info);
                    list.add(site);
                    map.put("data",list);
                }
            }
            map.put("state", true);
        } catch (IOException e) {
            e.printStackTrace();
            map.put("state", false);
        }
        return map;
    }

    // 获取行中单元格的值并赋值
    private Site getExcelSite(XSSFRow row) {
        Site site =new Site();
        if (row.getCell(0)!=null){
            String lineNo = row.getCell(0).getStringCellValue();
            row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
            site.setLineNo(lineNo);
        }
        if (row.getCell(1)!=null){
            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
            String lineName = row.getCell(1).getStringCellValue();
            site.setLineName(lineName);
        }
        if (row.getCell(2)!=null){
            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
            String stationName = row.getCell(2).getStringCellValue();
            site.setStationName(stationName);
        }
        if (row.getCell(3)!=null){
            row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);

            String location = row.getCell(3).getStringCellValue();
            site.setLocation(location);
        }
        if (row.getCell(4)!=null){
            row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);

            String type = row.getCell(4).getStringCellValue();
            site.setType(type);
        }
        return site;
    }

    // 获取行中单元格的值并赋值
    private Info getExcelInfo(XSSFRow row) {
        Info info =new Info();
        if (row.getCell(0)!=null){
            row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
            String lineNo = row.getCell(0).getStringCellValue();
            info.setLineNoId(lineNo);
        }
        if (row.getCell(5)!=null){
            row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
            String type1 = row.getCell(5).getStringCellValue();
            info.setType1(type1);
        }
        if (row.getCell(6)!=null){
            row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
            String type1Code = row.getCell(6).getStringCellValue();
            info.setType1Code(type1Code);
        }
        if (row.getCell(7)!=null){
            row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
            String pass1 = row.getCell(7).getStringCellValue();
            info.setPass1(pass1);
        }
        if (row.getCell(8)!=null){
            row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
            String type2 = row.getCell(8).getStringCellValue();
            info.setType2(type2);
        }
        if (row.getCell(9)!=null){
            row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
            String type2Code = row.getCell(9).getStringCellValue();
            info.setType2Code(type2Code);
        }
        if (row.getCell(10)!=null){
            row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
            String pass2 = row.getCell(10).getStringCellValue();
            info.setPass2(pass2);
        }
        if (row.getCell(11)!=null){
            row.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
            String type3 = row.getCell(11).getStringCellValue();
            info.setType3(type3);
        }
        if (row.getCell(12)!=null){
            row.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
            String type3Code = row.getCell(12).getStringCellValue();
            info.setType3Code(type3Code);
        }
        if (row.getCell(13)!=null){
            row.getCell(13).setCellType(Cell.CELL_TYPE_STRING);
            String pass3 = row.getCell(13).getStringCellValue();
            info.setPass3(pass3);
        }
        if (row.getCell(14)!=null){
            row.getCell(14).setCellType(Cell.CELL_TYPE_STRING);
            String type4 = row.getCell(14).getStringCellValue();
            info.setType4(type4);
        }
        if (row.getCell(15)!=null){
            row.getCell(15).setCellType(Cell.CELL_TYPE_STRING);
            String type4Code = row.getCell(15).getStringCellValue();
            info.setType4Code(type4Code);
        }
        if (row.getCell(16)!=null){
            row.getCell(16).setCellType(Cell.CELL_TYPE_STRING);
            String pass4 = row.getCell(16).getStringCellValue();
            info.setPass4(pass4);
        }
        if (row.getCell(17)!=null){
            row.getCell(17).setCellType(Cell.CELL_TYPE_STRING);
            String type5 = row.getCell(17).getStringCellValue();
            info.setType5(type5);
        }
        if (row.getCell(18)!=null){
            row.getCell(18).setCellType(Cell.CELL_TYPE_STRING);
            String type5Code = row.getCell(18).getStringCellValue();
            info.setType5Code(type5Code);
        }
        if (row.getCell(19)!=null){
            row.getCell(19).setCellType(Cell.CELL_TYPE_STRING);
            String pass5 = row.getCell(19).getStringCellValue();
            info.setPass5(pass5);
        }
        if (row.getCell(20)!=null){
            row.getCell(20).setCellType(Cell.CELL_TYPE_STRING);
            String type6 = row.getCell(20).getStringCellValue();
            info.setType6(type6);
        }
        if (row.getCell(21)!=null){
            row.getCell(21).setCellType(Cell.CELL_TYPE_STRING);
            String type6Code = row.getCell(21).getStringCellValue();
            info.setType6Code(type6Code);
        }
        if (row.getCell(22)!=null){
            row.getCell(22).setCellType(Cell.CELL_TYPE_STRING);
            String pass6 = row.getCell(22).getStringCellValue();
            info.setPass6(pass6);
        }
        if (row.getCell(23)!=null){
            row.getCell(23).setCellType(Cell.CELL_TYPE_STRING);
            String type7 = row.getCell(23).getStringCellValue();
            info.setType7(type7);
        }
        if (row.getCell(24)!=null){
            row.getCell(24).setCellType(Cell.CELL_TYPE_STRING);
            String type7Code = row.getCell(24).getStringCellValue();
            info.setType7Code(type7Code);
        }
        if (row.getCell(25)!=null){
            row.getCell(25).setCellType(Cell.CELL_TYPE_STRING);
            String pass7 = row.getCell(25).getStringCellValue();
            info.setPass7(pass7);
        }
        if (row.getCell(26)!=null){
            row.getCell(26).setCellType(Cell.CELL_TYPE_STRING);
            String type8 = row.getCell(26).getStringCellValue();
            info.setType8(type8);
        }
        if (row.getCell(27)!=null){
            row.getCell(27).setCellType(Cell.CELL_TYPE_STRING);
            String type8Code = row.getCell(27).getStringCellValue();
            info.setType8Code(type8Code);
        }
        if (row.getCell(28)!=null){
            row.getCell(28).setCellType(Cell.CELL_TYPE_STRING);
            String pass8 = row.getCell(28).getStringCellValue();
            info.setPass8(pass8);
        }
        if (row.getCell(29)!=null){
            row.getCell(29).setCellType(Cell.CELL_TYPE_STRING);
            String type9 = row.getCell(29).getStringCellValue();
            info.setType9(type9);
        }
        if (row.getCell(30)!=null){
            row.getCell(30).setCellType(Cell.CELL_TYPE_STRING);
            String type9Code = row.getCell(30).getStringCellValue();
            info.setType9Code(type9Code);
        }
        if (row.getCell(31)!=null){
            row.getCell(31).setCellType(Cell.CELL_TYPE_STRING);
            String pass9 = row.getCell(31).getStringCellValue();
            info.setPass9(pass9);
        }
        if (row.getCell(32)!=null){
            row.getCell(32).setCellType(Cell.CELL_TYPE_STRING);
            String type10 = row.getCell(32).getStringCellValue();
            info.setType10(type10);
        }
        if (row.getCell(33)!=null){
            row.getCell(33).setCellType(Cell.CELL_TYPE_STRING);
            String type10Code = row.getCell(33).getStringCellValue();
            info.setType10Code(type10Code);
        }
        return info;
    }

    //批量导入设备2
    @PostMapping("importExcel2")
    @ResponseBody //importExcel是弹出框name值
    public Map<String, Object> importExcel2(@RequestParam("file") MultipartFile importExcel){
        Map<String, Object> map = new HashMap<>();
        try {
            //1.将文件封装成工作薄
            XSSFWorkbook workbook = new XSSFWorkbook(importExcel.getInputStream());
            //2.获取工作表的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            //3.循环所有的工作表，根据工作表下标获取对应的sheet
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = workbook.getSheetAt(i);
                // 4.获取当前工作表中数据的开始位置
                int firstRowNum = sheet.getFirstRowNum();
                // 5.获取当前工作表中数据的结束位置
                int lastRowNum = sheet.getLastRowNum();
                // 6.将文件中的数据封装为一个list保存到数据库中
                List<Site> list = new ArrayList<Site>();
                for (int j = firstRowNum+1; j <= lastRowNum; j++) {
                    // 获取sheet中的每一行
                    XSSFRow row = sheet.getRow(j);
                    // 获取行中单元格的值
                    Device device = getExcelDevice(row);
                    //插入
                    deviceService.ImportAdd(device);
                }
            }
            map.put("state", true);
        } catch (IOException e) {
            e.printStackTrace();
            map.put("state", false);
        }
        return map;
    }

    // 获取行中单元格的值并赋值
    private Device getExcelDevice(XSSFRow row) {
        Device device =new Device();
        if (row.getCell(0)!=null){
            row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
            String lineNo = row.getCell(0).getStringCellValue();
            device.setLineNo(lineNo);
        }
        if (row.getCell(1)!=null){
            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
            String lineName = row.getCell(1).getStringCellValue();
            device.setLineName(lineName);
        }
        if (row.getCell(2)!=null){
            row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
            String sNumber = row.getCell(2).getStringCellValue();
            device.setSNumber(sNumber);
        }
        if (row.getCell(3)!=null){
            row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
            String GPartNumber = row.getCell(3).getStringCellValue();
            device.setGPartNumber(GPartNumber);
        }
        if (row.getCell(4)!=null){
            row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
            String GPartName = row.getCell(4).getStringCellValue();
            device.setGPartName(GPartName);
        }
        if (row.getCell(5)!=null){
            row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
            String RPartNumber = row.getCell(5).getStringCellValue();
            device.setRPartNumber(RPartNumber);
        }
        if (row.getCell(6)!=null){
            row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
            String RPartName = row.getCell(6).getStringCellValue();
            device.setRPartName(RPartName);
        }
        if (row.getCell(7)!=null){
            row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
            String MPartNumber = row.getCell(7).getStringCellValue();
            device.setMPartNumber(MPartNumber);
        }
        if (row.getCell(8)!=null){
            row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
            String MPartName = row.getCell(8).getStringCellValue();
            device.setMPartName(MPartName);
        }
        if (row.getCell(9)!=null){
            row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
            String DPartNumber = row.getCell(9).getStringCellValue();
            device.setDPartNumber(DPartNumber);
        }
        if (row.getCell(10)!=null){
            row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
            String DPartName = row.getCell(10).getStringCellValue();
            device.setDPartName(DPartName);
        }
        if (row.getCell(11)!=null){
            row.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
            String CPartNumber = row.getCell(11).getStringCellValue();
            device.setCPartNumber(CPartNumber);
        }
        if (row.getCell(12)!=null){
            row.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
            String CPartName = row.getCell(12).getStringCellValue();
            device.setCPartName(CPartName);
        }
        if (row.getCell(13)!=null){
            row.getCell(13).setCellType(Cell.CELL_TYPE_STRING);
            String EPartNumber = row.getCell(13).getStringCellValue();
            device.setEPartNumber(EPartNumber);
        }
        if (row.getCell(14)!=null){
            row.getCell(14).setCellType(Cell.CELL_TYPE_STRING);
            String EPartName = row.getCell(14).getStringCellValue();
            device.setEPartName(EPartName);
        }
        if (row.getCell(15)!=null){
            row.getCell(15).setCellType(Cell.CELL_TYPE_STRING);
            String SPartNumber = row.getCell(15).getStringCellValue();
            device.setSPartNumber(SPartNumber);
        }
        if (row.getCell(16)!=null){
            row.getCell(16).setCellType(Cell.CELL_TYPE_STRING);
            String SPartName = row.getCell(16).getStringCellValue();
            device.setSPartName(SPartName);
        }
        if (row.getCell(17)!=null){
            row.getCell(17).setCellType(Cell.CELL_TYPE_STRING);
            String CoPartNumber = row.getCell(17).getStringCellValue();
            device.setCoPartNumber(CoPartNumber);
        }
        if (row.getCell(18)!=null){
            row.getCell(18).setCellType(Cell.CELL_TYPE_STRING);
            String CoPartName = row.getCell(18).getStringCellValue();
            device.setCoPartName(CoPartName);
        }
        if (row.getCell(19)!=null){
            row.getCell(19).setCellType(Cell.CELL_TYPE_STRING);
            String IpcPartNumber = row.getCell(19).getStringCellValue();
            device.setIpcPartNumber(IpcPartNumber);
        }
        if (row.getCell(20)!=null){
            row.getCell(20).setCellType(Cell.CELL_TYPE_STRING);
            String IpcPartName = row.getCell(20).getStringCellValue();
            device.setIpcPartName(IpcPartName);
        }
        if (row.getCell(21)!=null){
            row.getCell(21).setCellType(Cell.CELL_TYPE_STRING);
            String QrPartNumber = row.getCell(21).getStringCellValue();
            device.setQrPartNumber(QrPartNumber);
        }
        if (row.getCell(22)!=null){
            row.getCell(22).setCellType(Cell.CELL_TYPE_STRING);
            String QrPartName = row.getCell(22).getStringCellValue();
            device.setQrPartName(QrPartName);
        }
        if (row.getCell(23)!=null){
            row.getCell(23).setCellType(Cell.CELL_TYPE_STRING);
            String FPartNumber = row.getCell(23).getStringCellValue();
            device.setFPartNumber(FPartNumber);
        }
        if (row.getCell(24)!=null){
            row.getCell(24).setCellType(Cell.CELL_TYPE_STRING);
            String FPartName = row.getCell(24).getStringCellValue();
            device.setFPartName(FPartName);
        }
        if (row.getCell(25)!=null){
            row.getCell(25).setCellType(Cell.CELL_TYPE_STRING);
            String TPartNumber = row.getCell(25).getStringCellValue();
            device.setTPartNumber(TPartNumber);
        }
        if (row.getCell(26)!=null){
            row.getCell(26).setCellType(Cell.CELL_TYPE_STRING);
            String TPartName = row.getCell(26).getStringCellValue();
            device.setTPartName(TPartName);
        }
        if (row.getCell(27)!=null){
            row.getCell(27).setCellType(Cell.CELL_TYPE_STRING);
            String MdPartNumber = row.getCell(27).getStringCellValue();
            device.setMdPartNumber(MdPartNumber);
        }
        if (row.getCell(28)!=null){
            row.getCell(28).setCellType(Cell.CELL_TYPE_STRING);
            String MdPartName = row.getCell(28).getStringCellValue();
            device.setMdPartName(MdPartName);
        }
        if (row.getCell(29)!=null){
            row.getCell(29).setCellType(Cell.CELL_TYPE_STRING);
            String MlPartNumber = row.getCell(29).getStringCellValue();
            device.setMlPartNumber(MlPartNumber);
        }
        if (row.getCell(30)!=null){
            row.getCell(30).setCellType(Cell.CELL_TYPE_STRING);
            String MlPartName = row.getCell(30).getStringCellValue();
            device.setMlPartName(MlPartName);
        }
        if (row.getCell(31)!=null){
            row.getCell(31).setCellType(Cell.CELL_TYPE_STRING);
            String SlPartNumber = row.getCell(31).getStringCellValue();
            device.setSlPartNumber(SlPartNumber);
        }
        if (row.getCell(32)!=null){
            row.getCell(32).setCellType(Cell.CELL_TYPE_STRING);
            String SlPartName = row.getCell(32).getStringCellValue();
            device.setSlPartName(SlPartName);
        }
        if (row.getCell(33)!=null){
            row.getCell(33).setCellType(Cell.CELL_TYPE_STRING);
            String picture = row.getCell(33).getStringCellValue();
            device.setPicture(picture);
        }
        return device;
    }
}